package com.zkh.myutils.scheduled.api;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.zkh.myutils.database.DatabaseException;
import com.zkh.myutils.database.conutils.PoolUtils;
import com.zkh.myutils.database.data.QueryRunner;
import com.zkh.myutils.database.data.handler.MapHandler;
import com.zkh.myutils.database.data.handler.MapListHandler;
import com.zkh.myutils.database.data.handler.ObjectHandler;
import com.zkh.myutils.database.dbpool.ConnectConfigure;
import com.zkh.myutils.database.dbpool.DBType;
import com.zkh.myutils.io.log.Logger;
import com.zkh.myutils.page.PageUtil;
import com.zkh.myutils.scheduled.ScheduledTaskConfig;
import com.zkh.myutils.scheduled.ScheduledTaskException;
import com.zkh.myutils.scheduled.ScheduledTaskScanner;
import com.zkh.myutils.scheduled.cron.CronBean;
import com.zkh.myutils.utils.StringUtils;

/**
 * 定时任务服务API，供外部调用
 * @author zkh
 */
public class ScheduledTaskService {

	//数据库操作对象
	private QueryRunner qr = new QueryRunner(PoolUtils.getDataSource());
	//数据库类型
	private DBType dbType = ConnectConfigure.getDefaultSourceConfig().getDbtype();
	
	/**
	 * 查询任务列表
	 * @return
	 */
	public PageUtil<Map<String, Object>> qryScheduledTaskRecord(ScheduledTaskListQueryParamBean paramBean){
		//查询条件和参数
		StringBuilder sb = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		//组装条件
		if(StringUtils.isNotEmpty(paramBean.getJobEcd())) {
			sb.append("and a.jobEcd=? ");
			params.add(paramBean.getJobEcd());
		}
		if(StringUtils.isNotEmpty(paramBean.getJobDesc())) {
			sb.append("and b.jobDesc like ? ");
			params.add("%" + paramBean.getJobDesc() + "%");
		}
		if(paramBean.getExecuteType()!=null) {
			sb.append("and a.executeType=? ");
			params.add(paramBean.getExecuteType());
		}
		if(paramBean.getExecState()!=null) {
			sb.append("and a.execState=? ");
			params.add(paramBean.getExecState());
		}
		if(paramBean.getStartTime()!=null) {
			sb.append("and a.startTime>=? ");
			params.add(paramBean.getStartTime());
		}
		if(paramBean.getEndTime()!=null) {
			sb.append("and a.endTime<=? ");
			params.add(paramBean.getEndTime());
		}
		//格式化条件
		String where = sb.length()==0 ? "" : (" where " + sb.substring(4));
		//分页参数
		int pageIndex = paramBean.getPageIndex(), pageSize = paramBean.getPageSize();
		pageIndex = pageIndex<=0 ? 1 : pageIndex;pageSize=pageSize<=0 ? 10 : pageSize;
		//两个前缀拼接新前缀
		String prefix = paramBean.getTablePrefix() + paramBean.getTaskRecordTablePre(ScheduledTaskScanner.getTaskRecordTablePre());
		//查询数据SQL
		String countSql = "SELECT count(1) FROM " + prefix + "timer_task_record a LEFT JOIN " + prefix + "timer_task_def b ON a.jobEcd=b.jobEcd " + where;
		//查询数据
		long totalItems = Long.parseLong(qr.query(countSql, new ObjectHandler<Object>(), params.toArray()).toString());
		//分页对象
		PageUtil<Map<String, Object>> page = new PageUtil<Map<String, Object>>(pageSize, pageIndex, totalItems);
		//数据容器
		List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
		//有数据
		if(totalItems>0) {
			//开始索引
			int start = (pageIndex-1) * pageSize;
			//查询SQL
			String qrySQL = "SELECT "
					+ "    a.jobEcd,b.jobDesc,a.executeTime,a.executeType,a.execParam,a.startTime,a.endTime,a.execState,a.execResult "
					+ "FROM " + prefix + "timer_task_record a "
					+ "LEFT JOIN " + prefix + "timer_task_def b ON a.jobEcd=b.jobEcd "
					+ where
					+ "ORDER BY endTime DESC"
					+ " LIMIT " + start + "," + pageSize;
			//非Mysql
			if(DBType.TYPE_MYSQL!=dbType) {
				qrySQL = "select\r\n"
						+ "  jobEcd,jobDesc,executeTime,executeType,execParam,startTime,endTime,execState,execResult\r\n"
						+ "from (\r\n"
						+ "  SELECT\r\n"
						+ "    a.jobEcd,b.jobDesc,a.executeTime,a.executeType,a.execParam,a.startTime,a.endTime,a.execState,a.execResult,\r\n"
						+ "    row_number() over (order by endTime desc) as nums\r\n"
						+ "  FROM " + prefix + "timer_task_record a \r\n"
						+ "  LEFT JOIN " + prefix + "timer_task_def b ON a.jobEcd=b.jobEcd\r\n"
						+ where
						+ ") t where nums between "+(start+1)+" and " + (start + pageSize);
			}
			//查询
			if(paramBean.getDataSource()==null) {
				data = qr.query(qrySQL, new MapListHandler(), params.toArray());
			}else {
				//用指定数据源获取连接
				try(Connection conn = paramBean.getDataSource().getConnection()){
					data = qr.query(conn, qrySQL, new MapListHandler(), params.toArray());
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
		//保存数据
		page.setDataList(data);
		//返回分页对象
		return page;
	}
	
	/**
	 * 重新执行任务
	 * @param jobEcd 任务编号
	 * @param executeTime 执行点
	 */
	public void reExecute(String jobEcd, long executeTime) {
		//检查为空
		if(StringUtils.isEmpty(jobEcd)) {
			throw new ScheduledTaskException("任务编码不能为空");
		}
		//查询既有任务记录
		Map<String, Object> map = qr.query("SELECT jobEcd,executeTime,execParam FROM " + ScheduledTaskScanner.getTaskRecordTablePre() + "timer_task_record WHERE jobEcd=? AND executeTime=?", new MapHandler(), jobEcd, executeTime);
		//为空
		if(map==null) {
			throw new ScheduledTaskException("指定任务不存在");
		}
		//任务配置
		ScheduledTaskConfig config = ScheduledTaskScanner.geTaskConfig(jobEcd);
		//为空
		if(config==null) {
			throw new ScheduledTaskException("不存在任务配置，无法执行");
		}
		//当前执行时间（时间点）
		long execTime = Long.parseLong(CronBean.getFormatTime(CronBean.getNowTimeParts()));
		//新增SQL
		String insert = "INSERT INTO " + ScheduledTaskScanner.getTaskRecordTablePre() + "timer_task_record (jobEcd, executeTime, executeType, startTime, execState) VALUES (?, ?, 1, ?, 1)";
		//插入一条记录
		qr.update(insert, jobEcd, execTime, DBType.TYPE_MYSQL.equals(dbType) ? new Date() : new Timestamp(System.currentTimeMillis()));
		
		//执行结果和信息
		int success = 1; String msg = "";
		try {
			//执行任务
			config.getScheduledTask().execute(config.getJobParamJson());
			//信息
			msg = "执行成功";
		}catch(Exception e) {
			//打印异常
			Logger.error(e);
			//保存状态
			success = 0;
			msg = e.getMessage();
		}
		
		//更新SQL
		String update = "UPDATE " + ScheduledTaskScanner.getTaskRecordTablePre() + "timer_task_record SET endTime=?,execParam=?,execState=?,execResult=? WHERE jobEcd=? AND executeTime=?";
		//执行更新
		qr.update(update, DBType.TYPE_MYSQL.equals(dbType) ? new Date() : new Timestamp(System.currentTimeMillis()), config.getJobParamJson().toString(), success==1 ? 2 : 3, msg, jobEcd, execTime);
	}
}
